Kinetica

Overview

Kinetica uses SQL as its query language. Besides the traditional relational model, Kinetica provides other models including vector, graph, and time series.
https://docs.kinetica.com/6.2/concepts/sql.html
https://docs.kinetica.com/7.2/sql/

JSON

Details on JSON support can be found at https://docs.kinetica.com/7.2/concepts/json/. JSON is stored in Kinetica as strings. A variety of SQL functions are available for JSON interactions.
Consider this simple table and content.

CREATE TABLE "breynolds_qarbine"."product1 (
    "name" VARCHAR NOT NULL,
    "price" REAL NOT NULL,
    "details" JSON NOT NULL
     );
INSERT INTO product1
VALUES ( 'phone case', 24.50, 
'{"brand": "Fujiama", "color": "red", "sizes": [ "small", "medium"], "weights": [ 0.5, 0.6] }' )

The functions JSON_QUERY and JSON_VALUE are commonly used functions. They are described briefly below.

Function	Description
JSON_QUERY	Returns the JSON object at the given path in json; It returns null if the path doesn't exist or contains a primitive (non-object) value.
JSON_VALUE	Returns the JSON value at the given path in json. It returns null, if the path doesn't exist or contains an object (non-primitive) value

Consider the following query.

SELECT *, details as about
, JSON_QUERY(details, '$.sizes') as sizes
,JSON_VALUE(details, '$.brand') as brand
from product1

The details of the first product are shown below.

  

Notice that the JSON object values in the answer set are returned as strings. This is true even for this example snippet,

JSON_VALUE( '{"val" : 123}', '$.val') as val

Qarbine’s pragma feature can be used to turn the string into a JSON object or primitive value.
This is much more meaningful to interact with and requires no coding!

#pragma convertToObject about, sizes
SELECT *, details as about
, JSON_QUERY(details, '$.sizes') as sizes
,JSON_VALUE(details, '$.brand') as brand
from product1

The details of the first product are shown below.

  

Notice the values for sizes and about are now real JSON objects. Multiple Qarbine pragmas can be applied to answer sets to manipulate the returned list. Qarbine’s template processing is built to handle such objects including very dynamic ones in the same result list.

See the details on using Qarbine pragmas in the Data Source Designer guide.

Vector Searches

Kinetica's vector search capability can be used to query a table with a vector type column. The rows have embedding values which can be referenced to perform a variety of K-nearest neighbor searches. Details on JSON support can be found at
https://docs.kinetica.com/7.2/vector_search/

Qarbine’s embedding macro function can be used to dynamically obtain an embedding to be used in a query. Within a SQL query it has the pattern

[! embedding(phrase, AI_Assistant_alias) !]

The Qarbine administrator defines the AI assistant aliases. The one specified must return an embedding from the same model in which the row embedding values were obtained.

Embeddings may also be prompted for or passed in as runtime variable. One approach to using the embedding value in a SQL query is shown below.

SELECT TOP 3 *, 
embedding <-> VECTOR( [! string(@embedding) !], 3) as distance 
FROM breynolds_qarbine.vector1
ORDER BY distance

Vector searches are usually applied to locate similar rows based on the concept of distance. THe embedding values represent a point in n-dimensional space. Common algorithms for computing this value are Cosine distance, dot product, and Euclidean distance.

Geospatial

Qarbine provides geospatial related features ranging from map oriented prompting to obtain a location, to presenting locations on a map and Google Map linking. See the “Template Techniques” section of the online documentation for map prompting information. See the “Template Techniques;Custom Cells” section of the online documentation for details.

Details on geospatial support can be found at https://docs.kinetica.com/7.2/location_intelligence/

Graph Data

Qarbine provides graph data aware features to present nodes and edges as template output and also to interact with a set of nodes and edges in a window. See the “Template Techniques;Custom Cells” section of the online documentation for details.

Details on graph data support can be found at https://docs.kinetica.com/7.2/sql/graph/

Virtual Catalogs

The Kinetica Virtual Catalog tables contain metadata about the database's objects, relationships, & permissions. Qarbine provides a set of DBA oriented data sources and templates. See the Kinetica area within the “DBA Tutorials” section of the online documentation.

Some useful queries can be found at https://docs.kinetica.com/7.2/snippets/virtual-catalog-queries/

For more details see https://docs.kinetica.com/7.2/catalogs/kinetica/

Data Types

Kinetica supports a wide variety of data types to supports is JSON, geo-spatial, time series and other features. Details on can be found at https://docs.kinetica.com/6.2/concepts/types.html#types-chart

Troubleshooting

If the query used within Qarbine is not yielding the anticipated results, then the Kinetica workbench can be used to test queries. Ad hoc queries can be run within a workbook. You can create a temporary one if needed.

  

Enter the SQL in question to the right of   .

  

Click on the highlighted button to run the SQL.

  

If an error occurs is will be shown,

  

Otherwise the results will be shown in the data tab.

  

More information on the workbench can be found at https://docs.kinetica.com/7.2/admin/workbench/